Looping through a column and pasting data into another sheet for external data refresh

Dav3yRox

New Member
Joined
Aug 26, 2021
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Good morning all,

I'm hoping that someone could shed some light on this as I'm new to the whole looping side of VBA.
My aim is to read down a selected set of data (Columns N and O on Sheet("GC4") and place the relative data in cells C3 and C4 on Store Data.

the data from column N required to be populated into C4, and Column O into C3.
This will then need to refresh the external data table located on Sheet "StoreData"
This will then copy the data from StoreData onto Outputs.
Once it has copied the data, it should then cycle through all rows on the GC4 tab and repeat the refresh for each one until the cells are blank.

I have uploaded the code I have got below, however I keep getting a Runtime Error '1004': Application Defined or object-defined error.
Any help would be greatly appreciated!


Dim RunLoop As Boolean
Dim OutputRow As Long
RunLoop = True
'Update Starting Report Date
Sheets("StoreData").Range("C3").Value = Sheets("GC4 AC ").Range("O4").Value
Sheets("StoreData").Range("C4").Value = Sheets("GC4 AC ").Range("N4").Value

Do While (RunLoop = True)
'Check Output Row Number
OutputRow = Application.WorksheetFunction.CountA(Sheets("Control").Range("B17:B100000")) + 17

Sheets("StoreData").Select
Sheets("StoreData").Range("Table_Query_from_GCOT2F244[[#Headers],[ODTDEPO]]").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

Range("F2:P2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Sheets("Outputs").Select
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'Move to next date
Sheets("StoreData").Range("C3").Value = Sheets("StoreData").Range("O4").Offset(1,0)
Sheets("StoreData").Range("C4").Value = Sheets("StoreData").Range("N4").Offset(1,0)
If (Sheets("StoreData").Range("C3").Value > Sheets("Control").Range("C14").Value) Then
RunLoop = False
End If
Loop
MsgBox ("Complete")
End Sub
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Forum statistics

Threads
1,148,420
Messages
5,746,572
Members
424,032
Latest member
pochie2741

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