Excel Macro's copy data from one sheet to anotherSolved/Closed

Question

Hey Everyone

ok, so, I have a document with multiple sheets
one of the sheets is a reporting sheet
what i am trying to accomplish with not much luck is this.

on the reporting sheet, I would like to specify a date range, then pull out the rows from another sheet to the reporting sheet that are within that date range. the source data is something like

id    asset    serial    location    date

now when the rows are moved from the source sheet to the destination sheet, i only need to pull columns 2,3 and 5

The issue im having is pulling data from within a date range. All the examples I have found and managed to get working are all based on key words or values.

any help would be great. I am not a macro pro but i know my way around code.

Thanks :)

vcoolio · Answer

Hello Carolusclen, I'm not sure if I fully follow what you would like to do however, I believe that the following code may do the task for you:- Sub FindDetails() Dim val1 As Long: val1 = [B1] Dim val2 As Long: val2 = [C1] Dim lr As Long Dim ws As Worksheet Application.DisplayAlerts = False For Each ws In Worksheets If ws.Name <> "Report" Then ws.Range("E1", ws.Range("E" & ws.Rows.Count).End(xlUp)).AutoFilter 1, ">=" & val1, xlAnd, "<=" & val2 lr = ws.Range("E" & Rows.Count).End(xlUp).Row If lr > 1 Then Union(ws.Range("B2:C" & lr), ws.Range("E2:E" & lr)).Copy Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues End If ws.[E1].AutoFilter End If Next ws Application.CutCopyMode = False Application.DisplayAlerts = True End Sub Basically, the code finds all dates (from all sheets) within a date range that you enter in cells B1(From) and C1(To) in the "Report" sheet and when these dates are found, the data from each relevant row (from Columns B, C & E only) are transferred to the "Report" sheet. Following is the link to my test workbook for you to peruse:- https://www.dropbox.com/s/dbcbx86va6uve2w/Carolusclen%28Multi%20shts%20to%20master%20sht%2C%20union%29.xlsm?dl=0 There are already two dates in cells B1 and C1 in the "Report" sheet. Click on the "GO" button to see how it works. Change the dates as you wish then click on "GO" again. I hope that this helps. Cheerio, vcoolio.

vcoolio · Answer

Hello Carolusclen,

Can you advise which sheets you would like to exclude please.

Also, if there are merged cells, you will need to unmerge them as merged cells create havoc with VBA codes. You can easily reformat any sheet without merged cells.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Carolusclen,

If that's the case, then, in the code, change line 11 to:-

If ws.Name = "Refresh Log" Then

I'm assuming that the data in the "Refresh Log" sheet is not in a Pivot table.

Also, change line 8 to:-

Application.ScreenUpdating=False

and change line 26 to:-

Application.ScreenUpdating=True

Test it all in a copy of your workbook first.

To prevent duplication in the "Report" sheet, do you want the "used" data in the "Refresh Log" sheet cleared once it is transferred over or do you wish to keep it all in the "Refresh Log" sheet and just refresh the "Report" sheet on each transfer?

Cheerio,
vcoolio.

vcoolio · Answer

Hello Carolusclen,

You've made some changes that baffle me.

1) Is the column with the dates that need to be filtered Column E or Column P? Based on your opening post, you suggest that the date column is Column E. Please clarify this.

2) You have made reference to Sheet1:
If ws.Name = "Sheet1" Then

but you are transferring to Sheet1:

  Union(ws.Range("B2:C" & lr), ws.Range("E2:E" & lr)).Copy
Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues

so obviously this is not going to work as you are supposedly transferring from a source sheet to a destination sheet.

Can you advise the actual names of the source and destination sheets please.

Cheerio,
vcoolio.

vcoolio · Answer

Hello Carolusclen,

Turning ScreenUpdating Off/On should not make any difference to the code whatsoever. It basically just prevents screen flicker as Excel does its calculations.

I'm not sure what you've been up to but I do see some errors in what you've been attempting. Hence, I have altered the code a little as follows:-

Sub FindDetails()

Dim val1 As Long: val1 = [B1]
        Dim val2 As Long: val2 = [C1]
        Dim lr As Long
        Dim ws As Worksheet
        Dim ws1 As Worksheet
Set ws = Worksheets("Report")
Set ws1 = Worksheets("Refresh Log")

Application.ScreenUpdating = False

ws1.Range("P1", ws1.Range("P" & ws1.Rows.Count).End(xlUp)).AutoFilter 1, ">=" & val1, xlAnd, "<=" & val2
      lr = ws1.Range("P" & Rows.Count).End(xlUp).Row
         If lr > 1 Then
           Union(ws1.Range("B2:C" & lr), ws1.Range("P2:P" & lr)).Copy
             ws.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
               End If
                 ws1.[P1].AutoFilter

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

As you only need to reference two sheets, you'll notice that I have declared two worksheet variables and then set values to them. So, ws now wholly and solely refers to the "Report" sheet and ws1 wholly and solely refers to the "Refresh Log" sheet. In order for all to work as it should, name your sheet tabs the same. I prefer to reference sheets by their sheet codes but this appears to be causing you some confusion so I think that the above method may help you to understand more clearly the whole process.

The above code now filters Column P of the "Refresh Log" sheet (ws1) for the date range that you place in cells B1(from) and C1(to) in the "Report" sheet(ws). The relevant data is then transferred from The "Refresh Log" sheet to the "Report" sheet.
The code, as you can probably tell, excludes all other sheets and will only deal with the two sheets named in the code.

Following is the link to my updated test work book:-

https://www.dropbox.com/s/h9d9m7v9dw3y5qw/Carolusclen%28Multi%20shts%20to%20master%20sht%2C%20union%293.xlsm?dl=0

Play with the dates in the from and to (B1 & C1) cells in the "Report" sheet then click on "GO" to see it work.

Let me know which columns exactly you wish to extract the data from and I'll show you how to do it just in case your future data extraction range changes.

Test the above code in a copy of your work book first.

Place the above code in a separate module.

Cheerio,
vcoolio.

Excel Macro's copy data from one sheet to another

5 responses

Similar discussions

Subscribe To Our Newsletter!