Hi Dinesh, I'm not sure if you are still monitoring this page but I've tried to execute this code and am getting an error: Run-time error '9': Subscript out of range. The line in Question is Sheets("sheet5").range(Cells(i, 1), Cells(i, 2))).Copy Note: My sheet # is 5. When I hover over the Cells(i, 1) and Cells(i, 2) i do see the correct values of those cells in the other sheet.
I guess im asking the wrong place but does someone know a trick to get back into an instagram account..? I somehow lost my password. I would love any help you can offer me
@Rory Cayson Thanks for your reply. I found the site through google and I'm trying it out atm. Seems to take quite some time so I will get back to you later when my account password hopefully is recovered.
Hi Dinesh, Thanks for your tutorial. I am having some issues. I get a - run time error '9', Subscript out of Range. When I try to run this code. What is the issue? I have made sure it is referencing the right sheets but i don't understand the issue. Please help
Hi Dinesh. Thank you for your step by step video. I'd like some clarification on a 424 error I'm getting. I'm clueless about VBA so I've copied your formula exactly as you wrote it. The only difference is my first sheet is called "All" and the second sheet is called "Secular". The error highlights the 3rd line (lastrow=etc...) as erroneous. I can't figure out what's wrong. I saved the code as a xlsm file in order to enable macros. I inserted the button as you suggested and when I ran the macro, it gave me the error listed above. Here's my code. Sub copyNonBlankDataSecular() Dim erow As Long, lastrow As Long, i As Long lastrow = All.Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lastrow If All.Cells(i, 1) “” Then Sheets("All").Range(Cells(i, 1), Cells(i, 2)).Copy Sheets("Secular").Activate erow = Secular.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row ActiveSheet.Paste Destination:=Sheets("Secular").Range(Cells(erow, 1), Cells(erow, 2)) Sheets(“All”).Activate End If Next i Application.CutCopyMode = False End Sub
hi Dinesh i tried you code and it works. but i have more than 2 columns of data to copy and paste, when i added the other columns, i'm getting run time error 450 for the below line: For i = 3 To lastRow If Sheet1.Cells(i, 2) "" Then Sheets("sheet1").Range(Cells(i, 2), Cells(i, 3), Cells(i, 4)).Copy >>>> (run time error 450 when I added the Cells(i,4)) Sheets("sheet2").Activate erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row ActiveSheet.Paste Destination:=Sheets("sheet2").Range(Cells(erow, 1), Cells(erow, 2), Cells(erow, 3)) >>>>added Cells(erow,3) Sheets("sheet1").Activate End If I added the Cells(i,4) since i have 3 columns of data. how to write this properly?
@@Exceltrainingvideos Hi Dinesh. I would like to copy cells from specific coloumns A, C, G, K, N, V, W, AD, AF, if A is not blank. And I would like them to be pasted with no blank columns: A to A, C to B, G to C, K to D N to E.... Could you please help me with this code ? Thank you so much Brgds Thomas
i have a question i notice when you define the Lastrow you type ( lastrow=sheet1.cells(rows.count,1).end(xlup).row ) when i type that statement i get a compile error in VBA stating --> Compile Error: Expected: ( can you help resolve?
Dear sir, First o all, I would like to say thank you for your help. I have copied the same code from the link given, but I got an error 1004 saying " Application defined or object defined error" One point more, can I use this formula with rows with formulas? I need only the rows that has a result (Not "") to be copied and pasted. Thank you sir Thamer
@@Exceltrainingvideos yes, for example column A1:100 I have a data and there are few empty cells in middle. So I want to copy A1:100 with empty cells as well
sir plz make a video for how to tranasfer last entry row data automatically to onther workbook master sheet blank row . only data not formula . our header same two workbook. plz help sir
This should be pretty easy: 1. Identify the last row of data in the relevant worksheet 2. Copy the data 3. Open the other workbook 4. Find the next blank row 5. Paste the data You can optimize the code to one line of code by copying from the source and pasting it into the destination using suitable paths.