Joined
Dec 17, 2013
Messages
37
Hi, I have vba which works very well, it copies down to the last row and will paste on the first empty row, however when it is copying it skips blanks, does anybody know how my code could be modified so that it will copy and paste blanks? I'm copying and pasting from multiple workbooks so that is why I need the ability to paste on the next empty row.
Thanks in advance,
HeyYouYesYouNoNotYouYou,
Code:
Private Sub prde()'product description


Windows("sales.csv").Activate
Sheets("Sheet2").Select
Dim FindRng As Range
Dim c As Range
Dim First As String
Dim Rng As Range
Set FindRng = Range("L:L")
With FindRng
Set c = .Find(What:="*", After:=FindRng.Cells(1, 1), LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
First = c.Address
Set Rng = c
Do
Set Rng = Union(Rng, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> First
End If
Rng.Select
End With


Selection.Copy Destination:=Workbooks("Manifest").Sheets("Reports").Range("A" & Rows.Count).End(xlUp).Offset(1)
Range("A1").Select
Windows("Manifest").Activate
Sheets("Reports").Select
End Sub
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

ZAX

Well-known Member
Joined
Jul 5, 2012
Messages
715
Hey HeyYouYesYouNoNotYouYou,
The changes are in the red lines:
Code:
Private Sub prde()'product description


Windows("sales.csv").Activate
Sheets("Sheet2").Select
Dim FindRng As Range
Dim c As Range
Dim First As String
Dim Rng As Range
Set FindRng = Range("L:L")
With FindRng
Set c = .Find(What:="*", After:=FindRng.Cells(1, 1), LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
First = c.Address
Set Rng = c
Do
Set Rng = Union(Rng, c)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> First
End If
Rng.Select
End With


[COLOR="#FF0000"]Selection.Copy
Workbooks("Manifest").Sheets("Reports").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial(xlPasteAll)
Application.CutCopyMode = False[/COLOR]
Range("A1").Select
Windows("Manifest").Activate
Sheets("Reports").Select
End Sub

ZAX
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,157
Latest member
MrBJBones

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top